#!/usr/bin/env python
# -*- coding: utf-8 -*-

from flask import Flask, current_app, jsonify, request, Response, send_from_directory, send_file
from flask_cors import CORS
import datetime
import random
import json
import os
from blockchain_api import blockchain_bp
import pandas as pd
import mysql.connector
import hashlib
import jwt
from functools import wraps
from blockchain_api import create_record
from blockchain_api import blockchain_bp # 确保导入蓝图
from pypinyin import lazy_pinyin
import random
import tempfile
import pymysql
import traceback
from werkzeug.utils import secure_filename

app = Flask(__name__)
# 使用简单全局CORS配置
CORS(app, origins=["*"], supports_credentials=True, allow_headers="*", methods="*", expose_headers="*")

# 数据存储目录
DATA_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data')
if not os.path.exists(DATA_DIR):
    os.makedirs(DATA_DIR)

# 数据文件路径
SUPPLIERS_FILE = os.path.join(DATA_DIR, 'suppliers.json')
PRODUCTS_FILE = os.path.join(DATA_DIR, 'products.json')
SALES_FILE = os.path.join(DATA_DIR, 'sales.json')
WARNINGS_FILE = os.path.join(DATA_DIR, 'warnings.json')
SENSOR_DATA_FILE = os.path.join(DATA_DIR, 'sensor_data.json')

# 数据库配置
db_config = {
    'host': 'localhost',
    'port': 3306,  # 明确指定端口
    'user': 'root',  # 数据库用户名
    'passwd': '123456',  # 密码
    'db': 'premade_food_trace',  # 数据库名
    'charset': 'utf8mb4'  # 添加字符集
}

# JWT密钥 (保留原有配置)
JWT_SECRET = 'your-secret-key'

# 配置上传文件相关参数
UPLOAD_FOLDER = tempfile.gettempdir()
ALLOWED_EXTENSIONS = {'xlsx', 'xls'}
MAX_CONTENT_LENGTH = 10 * 1024 * 1024  # 10MB

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 获取数据库连接 - 使用pymysql
def get_db_connection():
    try:
        print("尝试建立数据库连接...")
        conn = pymysql.connect(
            host=db_config['host'],
            port=db_config['port'],
            user=db_config['user'],
            passwd=db_config['passwd'],
            db=db_config['db'],
            charset=db_config['charset'],
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功")
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        print("详细错误信息:")
        traceback.print_exc()
        raise

# 密码加密函数
def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

# 验证token的装饰器
def token_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        try:
            token = request.headers.get('Authorization')
            print("收到的token:", token)  # 添加日志
            
            if not token:
                print("未收到token")  # 添加日志
                return jsonify({'message': '缺少token'}), 401
                
            # 处理 Bearer 前缀
            if token.startswith('Bearer '):
                token = token[7:]
                print("处理后的token:", token)  # 添加日志
                
            try:
                data = jwt.decode(token, JWT_SECRET, algorithms=["HS256"])
                current_user = data['user']
                print("解析后的用户数据:", current_user)  # 添加日志
            except jwt.ExpiredSignatureError:
                print("token已过期")  # 添加日志
                return jsonify({'message': 'token已过期'}), 401
            except jwt.InvalidTokenError:
                print("token无效")  # 添加日志
                return jsonify({'message': 'token无效'}), 401
            except Exception as e:
                print(f"Token验证错误: {str(e)}")  # 添加日志
                return jsonify({'message': 'token验证失败'}), 401
                
            return f(current_user, *args, **kwargs)
            
        except Exception as e:
            print(f"装饰器错误: {str(e)}")  # 添加日志
            return jsonify({'message': '服务器错误'}), 500
            
    return decorated

# 初始化数据文件
def init_data_files():
    # 供应商数据
    if not os.path.exists(SUPPLIERS_FILE):
        suppliers = [
            {
                'id': '1',
                'name': '湖南农耕食品有限公司',
                'code': '农耕食品',
                'business_scope': '蔬菜加工',
                'phone': '13912345678',
                'address': '湖南省长沙市岳麓区',
                'foundTime': '2015-06-12'
            },
            {
                'id': '2',
                'name': '四川味宝食品科技有限公司',
                'code': '味宝',
                'business_scope': '调味料生产',
                'phone': '13887654321',
                'address': '四川省成都市高新区',
                'foundTime': '2010-03-25'
            }
        ]
        with open(SUPPLIERS_FILE, 'w', encoding='utf-8') as f:
            json.dump(suppliers, f, ensure_ascii=False, indent=2)
    
    # 产品数据
    if not os.path.exists(PRODUCTS_FILE):
        products = [
            {
                'id': '1',
                'name': '速冻水饺(韭菜猪肉)',
                'category': '速冻类',
                'supplier': '湖南农耕食品有限公司',
                'supplierId': '1',
                'price': 15.8,
                'tempMin': -18,
                'tempMax': -16,
                'humidityMin': 30,
                'humidityMax': 60,
                'shelfLife': 180,  # 保质期（天）
                'productionDate': '2023-03-10',
                'batchNo': 'BN2023032'
            },
            {
                'id': '2',
                'name': '半成品小炒肉',
                'category': '半成品类',
                'supplier': '四川味宝食品科技有限公司',
                'supplierId': '2',
                'price': 22.5,
                'tempMin': -5,
                'tempMax': 0,
                'humidityMin': 40,
                'humidityMax': 70,
                'shelfLife': 15,  # 保质期（天）
                'productionDate': '2023-03-20',
                'batchNo': 'BN2023028'
            }
        ]
        with open(PRODUCTS_FILE, 'w', encoding='utf-8') as f:
            json.dump(products, f, ensure_ascii=False, indent=2)
    
    # 预警数据
    if not os.path.exists(WARNINGS_FILE):
        warnings = [
            {
                'id': 'W2023032501',
                'warningTime': '2023-03-25 08:23:45',
                'productName': '速冻水饺(韭菜猪肉)',
                'productId': '1',
                'supplier': '湖南农耕食品有限公司',
                'supplierId': '1',
                'batch': 'BN2023032',
                'type': '温度异常',
                'value': '-12°C',
                'threshold': '-18°C',
                'status': '未处理'
            },
            {
                'id': 'W2023032502',
                'warningTime': '2023-03-25 09:15:22',
                'productName': '半成品小炒肉',
                'productId': '2',
                'supplier': '四川味宝食品科技有限公司',
                'supplierId': '2',
                'batch': 'BN2023028',
                'type': '湿度异常',
                'value': '78%',
                'threshold': '≤65%',
                'status': '未处理'
            }
        ]
        with open(WARNINGS_FILE, 'w', encoding='utf-8') as f:
            json.dump(warnings, f, ensure_ascii=False, indent=2)
    
    # 传感器数据
    if not os.path.exists(SENSOR_DATA_FILE):
        now = datetime.datetime.now()
        sensor_data = []
        
        # 生成一周的温度数据
        for i in range(7):
            day = now - datetime.timedelta(days=i)
            for hour in range(8, 20, 2):  # 每两小时一条数据
                time = day.replace(hour=hour, minute=0, second=0)
                # 产品1的数据
                temp = round(random.uniform(-18, -14), 1)  # 随机温度
                humidity = round(random.uniform(40, 60))   # 随机湿度
                sensor_data.append({
                    'timestamp': time.strftime('%Y-%m-%d %H:%M:%S'),
                    'productId': '1',
                    'productName': '速冻水饺(韭菜猪肉)',
                    'type': 'temperature',
                    'value': temp,
                    'status': '正常' if -18 <= temp <= -16 else '异常'
                })
                sensor_data.append({
                    'timestamp': time.strftime('%Y-%m-%d %H:%M:%S'),
                    'productId': '1',
                    'productName': '速冻水饺(韭菜猪肉)',
                    'type': 'humidity',
                    'value': humidity,
                    'status': '正常' if 30 <= humidity <= 60 else '异常'
                })
                
                # 产品2的数据
                temp = round(random.uniform(-5, 2), 1)  # 随机温度
                humidity = round(random.uniform(35, 75))  # 随机湿度
                sensor_data.append({
                    'timestamp': time.strftime('%Y-%m-%d %H:%M:%S'),
                    'productId': '2',
                    'productName': '半成品小炒肉',
                    'type': 'temperature',
                    'value': temp,
                    'status': '正常' if -5 <= temp <= 0 else '异常'
                })
                sensor_data.append({
                    'timestamp': time.strftime('%Y-%m-%d %H:%M:%S'),
                    'productId': '2',
                    'productName': '半成品小炒肉',
                    'type': 'humidity',
                    'value': humidity,
                    'status': '正常' if 40 <= humidity <= 70 else '异常'
                })
        
        with open(SENSOR_DATA_FILE, 'w', encoding='utf-8') as f:
            json.dump(sensor_data, f, ensure_ascii=False, indent=2)

# 初始化数据
init_data_files()

# 注册蓝图
app.register_blueprint(blockchain_bp)

# 辅助函数：读取JSON文件
def read_json_file(file_path):
    with open(file_path, 'r', encoding='utf-8') as f:
        return json.load(f)

# 辅助函数：写入JSON文件
def write_json_file(file_path, data):
    with open(file_path, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2)

# 管理员注册接口
@app.route('/api/admin/register', methods=['POST', 'OPTIONS'])
def register_admin():
    print(f"收到注册请求：{request.method} {request.path}")
    print(f"请求头：{request.headers}")
    
    if request.method == 'OPTIONS':
        print("处理OPTIONS预检请求")
        return '', 200
    
    try:
        data = request.get_json()
        print("接收到的注册数据:", data)  # 添加日志
        
        if not data:
            print("请求数据为空")
            return jsonify({'message': '请求数据不能为空'}), 400
            
        # 确保所有必填字段都存在
        required_fields = ['username', 'password', 'name']
        missing_fields = [field for field in required_fields if not data.get(field)]
        if missing_fields:
            print(f"缺少必填字段: {missing_fields}")
            return jsonify({'message': f'缺少必填字段: {", ".join(missing_fields)}'}), 400

        # 尝试使用数据库存储
        try:
            # 检查数据库连接和是否能够创建表
            print("尝试连接数据库...")
            conn = get_db_connection()
            print("数据库连接成功")
            cursor = conn.cursor()
            
            # 尝试创建 admins 表（如果不存在）
            print("检查 admins 表是否存在...")
            try:
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS admins (
                        id INT PRIMARY KEY AUTO_INCREMENT,
                        username VARCHAR(50) NOT NULL UNIQUE,
                        password VARCHAR(255) NOT NULL,
                        name VARCHAR(50) NOT NULL,
                        phone VARCHAR(20),
                        email VARCHAR(100),
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                ''')
                conn.commit()
                print("确保 admins 表存在")
            except mysql.connector.Error as err:
                print(f"创建表错误: {str(err)}")
                raise  # 重新抛出异常，让它进入文件存储流程

            # 检查用户名是否已存在
            try:
                cursor.execute('SELECT id FROM admins WHERE username = %s', (data['username'],))
                if cursor.fetchone():
                    print(f"用户名已存在: {data['username']}")
                    return jsonify({'message': '用户名已存在'}), 400
            except mysql.connector.Error as err:
                print(f"查询用户名错误: {str(err)}")
                raise  # 重新抛出异常，让它进入文件存储流程

            # 插入新管理员
            hashed_password = hash_password(data['password'])
            print("准备插入数据:", {
                'username': data['username'],
                'name': data['name'],
                'phone': data.get('phone'),
                'email': data.get('email')
            })
            
            try:
                cursor.execute('''
                    INSERT INTO admins (username, password, name, phone, email)
                    VALUES (%s, %s, %s, %s, %s)
                ''', (
                    data['username'],
                    hashed_password,
                    data['name'],
                    data.get('phone', ''),
                    data.get('email', '')
                ))
                
                conn.commit()
                print("管理员注册成功:", data['username'])  # 添加日志
            except mysql.connector.Error as err:
                print(f"插入数据错误: {str(err)}")
                raise  # 重新抛出异常，让它进入文件存储流程
            finally:
                cursor.close()
                conn.close()
            
            return jsonify({
                'message': '注册成功',
                'user': {
                    'username': data['username'],
                    'name': data['name']
                }
            }), 201

        # 数据库操作失败时，使用文件存储
        except mysql.connector.Error as err:
            print("数据库错误，使用文件存储替代:", str(err))
            
            # 使用文件存储管理员信息
            admin_file = os.path.join(DATA_DIR, 'admins.json')
            
            # 读取现有管理员
            admins = []
            if os.path.exists(admin_file):
                try:
                    with open(admin_file, 'r', encoding='utf-8') as f:
                        admins = json.load(f)
                except:
                    admins = []
            
            # 检查用户名是否存在
            if any(admin['username'] == data['username'] for admin in admins):
                return jsonify({'message': '用户名已存在'}), 400
            
            # 添加新管理员
            new_admin = {
                'id': len(admins) + 1,
                'username': data['username'],
                'password': hash_password(data['password']),
                'name': data['name'],
                'phone': data.get('phone', ''),
                'email': data.get('email', ''),
                'created_at': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }
            
            admins.append(new_admin)
            
            # 保存文件
            with open(admin_file, 'w', encoding='utf-8') as f:
                json.dump(admins, f, ensure_ascii=False, indent=2)
            
            print("管理员信息已保存到文件:", admin_file)
            
            return jsonify({
                'message': '注册成功 (文件模式)',
                'user': {
                    'username': data['username'],
                    'name': data['name']
                }
            }), 201

    except Exception as e:
        import traceback
        print("注册错误:", str(e))  # 添加日志
        print("错误详情:", traceback.format_exc())  # 添加详细错误信息
        return jsonify({'message': f'注册失败: {str(e)}'}), 500

# 添加详细的CORS调试
@app.after_request
def add_cors_headers(response):
    # 允许所有本地开发源
    allowed_origins = ['http://localhost:8082', 'http://127.0.0.1:8082']
    origin = request.headers.get('Origin', '')
    
    if origin in allowed_origins:
        response.headers['Access-Control-Allow-Origin'] = origin
        response.headers['Access-Control-Allow-Headers'] = 'Content-Type, Authorization, Cache-Control, Pragma, Expires'
        response.headers['Access-Control-Allow-Methods'] = 'GET, POST, PUT, DELETE, OPTIONS'
        response.headers['Access-Control-Allow-Credentials'] = 'true'
    
    return response

@app.before_request
def handle_preflight():
    if request.method == "OPTIONS":
        # 创建响应并添加CORS头
        resp = current_app.make_default_options_response()
        resp.headers['Access-Control-Allow-Origin'] = request.headers.get('Origin', '*')
        resp.headers['Access-Control-Allow-Headers'] = 'Content-Type, Authorization, Cache-Control, Pragma, Expires'
        resp.headers['Access-Control-Allow-Methods'] = 'GET, POST, PUT, DELETE, OPTIONS'
        resp.headers['Access-Control-Allow-Credentials'] = 'true'
        return resp
    

# 管理员登录接口
@app.route('/api/login', methods=['POST', 'OPTIONS'])
def login():
    print(f"收到登录请求：{request.method} {request.path}")
    print(f"请求头：{request.headers}")
    
    if request.method == 'OPTIONS':
        print("处理OPTIONS预检请求")
        return '', 200
        
    try:
        data = request.get_json()
        print("登录请求数据:", data)  # 添加日志记录
        
        if not data or not data.get('username') or not data.get('password'):
            return jsonify({'message': '用户名和密码不能为空'}), 400

        username_or_phone = data.get('username')
        password = data.get('password')
        hashed_password = hash_password(password)
        
        # 尝试从数据库获取用户信息
        try:
            print("尝试从数据库获取用户信息...")
            conn = get_db_connection()
            cursor = conn.cursor()

            # 查询用户 - 支持用户名或手机号登录
            cursor.execute('SELECT * FROM admins WHERE username = %s OR phone = %s', 
                          (username_or_phone, username_or_phone))
            user = cursor.fetchone()
            
            print("查询结果:", user)  # 添加日志记录
            
            cursor.close()
            conn.close()
            
            if not user:
                print("用户不存在")
                return jsonify({'message': '用户名或密码错误'}), 401
                
            if user['password'] != hashed_password:
                print("密码错误")
                return jsonify({'message': '用户名或密码错误'}), 401

            # 生成token
            token = jwt.encode({
                'user': {
                    'id': user['id'],
                    'username': user['username'],
                    'name': user['name']
                },
                'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=24)
            }, JWT_SECRET)
            
            print("登录成功，生成token")
            
            return jsonify({
                'token': f'Bearer {token}',
                'user': {
                    'id': user['id'],
                    'username': user['username'],
                    'name': user['name']
                }
            })
            
        # 如果数据库操作失败，尝试使用文件存储
        except mysql.connector.Error as err:
            print("数据库错误，尝试从文件获取用户信息:", str(err))
            
            # 从文件获取管理员信息
            admin_file = os.path.join(DATA_DIR, 'admins.json')
            
            if not os.path.exists(admin_file):
                print("管理员文件不存在")
                return jsonify({'message': '用户名或密码错误'}), 401
            
            try:
                with open(admin_file, 'r', encoding='utf-8') as f:
                    admins = json.load(f)
            except Exception as e:
                print(f"读取管理员文件失败: {str(e)}")
                return jsonify({'message': '读取用户信息失败'}), 500
            
            # 查找用户 - 支持用户名或手机号登录
            user = next((admin for admin in admins if 
                         admin['username'] == username_or_phone or 
                         admin['phone'] == username_or_phone), None)
            
            print("文件查询结果:", user)
            
            if not user:
                print("用户不存在(文件)")
                return jsonify({'message': '用户名或密码错误'}), 401
                
            if user['password'] != hashed_password:
                print("密码错误(文件)")
                return jsonify({'message': '用户名或密码错误'}), 401
            
            # 生成token
            token = jwt.encode({
                'user': {
                    'id': user['id'],
                    'username': user['username'],
                    'name': user['name']
                },
                'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=24)
            }, JWT_SECRET)
            
            print("登录成功，生成token(文件模式)")
            
            return jsonify({
                'token': f'Bearer {token}',
                'user': {
                    'id': user['id'],
                    'username': user['username'],
                    'name': user['name']
                }
            })

    except Exception as e:
        import traceback
        print(f"登录错误: {str(e)}")
        print("错误详情:", traceback.format_exc())
        return jsonify({'message': '登录失败'}), 500

# 获取供应商列表
@app.route('/api/suppliers', methods=['GET'])
def get_suppliers():
    try:
        print("=== 接收到获取供应商列表请求 ===")
        # 获取查询参数
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))

        keyword = request.args.get('keyword', '')
        supplier_type = request.args.get('type', '')
        
        print(f"查询参数: page={page}, pageSize={page_size}, keyword='{keyword}', type='{supplier_type}'")
        
    # 计算偏移量
        offset = (page - 1) * page_size
    
        print("尝试获取数据库连接...")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        where_clause = "1=1"
        if keyword:
            where_clause += f" AND (name LIKE '%{keyword}%' OR code LIKE '%{keyword}%' OR business_scope LIKE '%{keyword}%')"
        if supplier_type and supplier_type != '全部':
            where_clause += f" AND business_scope LIKE '%{supplier_type}%'"
        
        print(f"SQL查询条件: {where_clause}")
        
        # 查询总数
        try:
            count_sql = f"SELECT COUNT(*) as total FROM suppliers WHERE {where_clause}"
            print(f"执行SQL: {count_sql}")
            cursor.execute(count_sql)
            total = cursor.fetchone()['total']
            print(f"总记录数: {total}")
        except Exception as e:
            print(f"查询总数失败: {e}")
            traceback.print_exc()
            raise
        
        # 查询数据
        try:
            data_sql = f"""
                SELECT 
                    id,
                    name,
                    code,
                    contact_person,
                    phone,
                    address,
                    found_time,
                    business_scope,
                    license_number,
                    status
                FROM suppliers 
                WHERE {where_clause}
                ORDER BY id DESC 
                LIMIT {offset}, {page_size}
            """
            print(f"执行SQL: {data_sql}")
            cursor.execute(data_sql)
            suppliers = cursor.fetchall()
            print(f"查询到 {len(suppliers)} 条记录")
            if suppliers and len(suppliers) > 0:
                print(f"第一条记录: {suppliers[0]}")
        except Exception as e:
            print(f"查询数据失败: {e}")
            traceback.print_exc()
            raise
        
        # 关闭连接
        conn.close()
        print("数据库连接已关闭")
        
        # 创建响应对象
        print("创建响应...")
        response = jsonify({
        'status': 'success',
            'message': '获取成功',
        'total': total,
            'items': suppliers
        })
        
        # 添加缓存控制头，确保前端获取最新数据
        response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
        response.headers['Pragma'] = 'no-cache'
        response.headers['Expires'] = '0'
        
        print("响应准备完成，返回数据")
        return response

    except Exception as e:
        error_msg = f"获取供应商列表失败: {str(e)}"
        print(error_msg)
        print("详细错误信息:")
        traceback.print_exc()
        return jsonify({
            'status': 'error',
            'message': error_msg
        }), 500

# 添加供应商
@app.route('/api/suppliers', methods=['POST'])
def add_supplier():
    try:
        # 获取请求数据
        supplier = request.get_json()
        print(f"接收到添加供应商请求: {supplier}")
        
        # 数据验证
        required_fields = ['name', 'code', 'business_scope', 'phone']
        for field in required_fields:
            if field not in supplier or not supplier[field]:
                return jsonify({
                    'status': 'error',
                    'message': f'缺少必填字段: {field}'
                }), 400
        
        # 连接数据库
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 检查供应商名称是否已存在
        cursor.execute("SELECT id FROM suppliers WHERE name = %s", (supplier['name'],))
        if cursor.fetchone():
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '供应商名称已存在'
            }), 400
        
        # 检查供应商简称是否已存在
        cursor.execute("SELECT id FROM suppliers WHERE code = %s", (supplier['code'],))
        if cursor.fetchone():
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '供应商简称已存在'
            }), 400
        
        # 处理日期
        found_time = None
        if 'found_time' in supplier and supplier['found_time']:
            try:
                # 将日期转换为YYYY-MM-DD格式
                found_time = supplier['found_time']
            except Exception as e:
                print(f"日期转换失败: {e}")
                conn.close()
                return jsonify({
                    'status': 'error',
                    'message': f'日期格式错误: {e}'
                }), 400
        
        # 插入数据
        try:
            cursor.execute("""
                INSERT INTO suppliers 
                (name, code, business_scope, phone, address, found_time, status) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """, (
                supplier['name'],
                supplier['code'],
                supplier['business_scope'],
                supplier['phone'],
                supplier.get('address', ''),
                found_time,
                supplier.get('status', 'active')
            ))
            
            # 获取新插入记录的ID
            new_id = cursor.lastrowid
            
            # 提交事务
            conn.commit()
            print(f"添加供应商成功，ID: {new_id}")
            
            # 查询刚插入的记录完整信息
            cursor.execute("""
                SELECT id, name, code, business_scope, phone, address, found_time, status
                FROM suppliers WHERE id = %s
            """, (new_id,))
            
            new_supplier = cursor.fetchone()
            
            conn.close()
    
            return jsonify({
                'status': 'success',
                'message': '供应商添加成功',
                        'supplier': new_supplier
                    })
            
        except Exception as e:
            conn.rollback()
            conn.close()
            error_msg = f"添加供应商失败: {str(e)}"
            print(error_msg)
            return jsonify({
                'status': 'error',
                'message': error_msg
            }), 500
            
    except Exception as e:
        error_msg = f"添加供应商请求处理失败: {str(e)}"
        print(error_msg)
        return jsonify({
            'status': 'error',
            'message': error_msg
        }), 500

# 获取单个供应商
@app.route('/api/suppliers/<supplier_id>', methods=['GET'])
def get_supplier(supplier_id):
    try:
        print(f"获取供应商详情 ID: {supplier_id}")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 查询供应商信息
        cursor.execute("""
            SELECT 
                id,
                name,
                code,
                contact_person,
                phone,
                address,
                found_time,
                business_scope,
                license_number,
                status,
                created_at,
                updated_at
            FROM suppliers 
            WHERE id = %s
        """, (supplier_id,))
        
        supplier = cursor.fetchone()
        conn.close()
    
        if supplier:
                # 处理日期字段，使其可序列化
                if 'found_time' in supplier and supplier['found_time']:
                    supplier['found_time'] = supplier['found_time'].strftime('%Y-%m-%d')
                if 'created_at' in supplier and supplier['created_at']:
                    supplier['created_at'] = supplier['created_at'].strftime('%Y-%m-%d %H:%M:%S')
                if 'updated_at' in supplier and supplier['updated_at']:
                    supplier['updated_at'] = supplier['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
                    
                return jsonify({
                'status': 'success',
                'supplier': supplier
            })
        else:
            return jsonify({
                'status': 'error',
                'message': '供应商不存在'
            }), 404
    except Exception as e:
        print(f"获取供应商详情失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'获取供应商详情失败: {str(e)}'
        }), 500

# 更新供应商
@app.route('/api/suppliers/<supplier_id>', methods=['PUT'])
def update_supplier(supplier_id):
    try:
        supplier_data = request.get_json()
        print(f"更新供应商 ID: {supplier_id}, 数据: {supplier_data}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查供应商是否存在
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        existing_supplier = cursor.fetchone()
        
        if not existing_supplier:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '供应商不存在'
            }), 404
        
        # 处理成立时间字段
        found_time = None
        if 'found_time' in supplier_data and supplier_data['found_time']:
            if isinstance(supplier_data['found_time'], str):
                found_time = supplier_data['found_time']
        
        # 更新供应商信息
        update_fields = []
        update_values = []
        
        # 动态构建更新字段
        field_mappings = {
            'name': 'name',
            'code': 'code',
            'contact_person': 'contact_person',
            'phone': 'phone',
            'address': 'address',
            'business_scope': 'business_scope',
            'license_number': 'license_number',
            'status': 'status'
        }
        
        for client_field, db_field in field_mappings.items():
            if client_field in supplier_data and supplier_data[client_field] is not None:
                update_fields.append(f"{db_field} = %s")
                update_values.append(supplier_data[client_field])
        
        # 单独处理成立时间
        if found_time:
            update_fields.append("found_time = %s")
            update_values.append(found_time)
        
        # 添加更新时间
        update_fields.append("updated_at = NOW()")
        
        # 如果没有要更新的字段，直接返回成功
        if not update_fields:
            conn.close()
            return jsonify({
                'status': 'success',
                'message': '没有字段需要更新',
                'supplier': supplier_data
            })
    
        # 构建更新SQL
        update_sql = f"""
            UPDATE suppliers 
            SET {', '.join(update_fields)}
            WHERE id = %s
        """
        
        # 添加ID作为WHERE条件的值
        update_values.append(supplier_id)
        
        # 执行更新
        cursor.execute(update_sql, update_values)
        conn.commit()
        
        # 获取更新后的供应商信息
        cursor.execute("""
            SELECT 
                id,
                name,
                code,
                contact_person,
                phone,
                address,
                found_time,
                business_scope,
                license_number,
                status,
                created_at,
                updated_at
            FROM suppliers 
            WHERE id = %s
        """, (supplier_id,))
        
        updated_supplier = cursor.fetchone()
        conn.close()
        
        # 处理日期字段，使其可序列化
        if updated_supplier:
            if 'found_time' in updated_supplier and updated_supplier['found_time']:
                updated_supplier['found_time'] = updated_supplier['found_time'].strftime('%Y-%m-%d')
            if 'created_at' in updated_supplier and updated_supplier['created_at']:
                updated_supplier['created_at'] = updated_supplier['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if 'updated_at' in updated_supplier and updated_supplier['updated_at']:
                updated_supplier['updated_at'] = updated_supplier['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify({
            'status': 'success',
            'message': '供应商更新成功',
            'supplier': updated_supplier
        })
        
    except Exception as e:
        print(f"更新供应商失败: {str(e)}")
    return jsonify({
        'status': 'error',
            'message': f'更新供应商失败: {str(e)}'
        }), 500

# 删除供应商
@app.route('/api/suppliers/<supplier_id>', methods=['DELETE'])
def delete_supplier(supplier_id):
    try:
        print(f"删除供应商 ID: {supplier_id}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查供应商是否存在
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        existing_supplier = cursor.fetchone()
        
        if not existing_supplier:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '供应商不存在'
            }), 404
        
        # 检查供应商是否有关联数据
        try:
            cursor.execute("SELECT COUNT(*) as count FROM products WHERE supplier_id = %s", (supplier_id,))
            product_count = cursor.fetchone()['count']
            
            if product_count > 0:
                conn.close()
                return jsonify({
                    'status': 'error',
                    'message': f'无法删除该供应商，存在{product_count}个关联产品',
                    'related_products': product_count
                }), 400
        except Exception as e:
            # 如果产品表不存在，则忽略此检查
            print(f"检查关联产品时出错: {str(e)}")
        
        # 执行删除
        cursor.execute("DELETE FROM suppliers WHERE id = %s", (supplier_id,))
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        if affected_rows > 0:
            return jsonify({
                'status': 'success',
                'message': '供应商删除成功'
            })
        else:
            return jsonify({
                'status': 'error',
                'message': '供应商删除失败'
            }), 500
    
    except Exception as e:
        print(f"删除供应商失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'删除供应商失败: {str(e)}'
        }), 500

# 批量删除供应商
@app.route('/api/suppliers/batch-delete', methods=['POST'])
def batch_delete_suppliers():
    try:
        data = request.get_json()
        supplier_ids = data.get('ids', [])
        
        if not supplier_ids:
            return jsonify({
                'status': 'error',
                'message': '没有提供要删除的供应商ID'
            }), 400
        
        print(f"批量删除供应商 IDs: {supplier_ids}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 检查是否有关联产品
        suppliers_with_relations = []
        valid_ids_to_delete = []
        
        for supplier_id in supplier_ids:
            try:
                # 检查供应商是否存在
                cursor.execute("SELECT name FROM suppliers WHERE id = %s", (supplier_id,))
                supplier = cursor.fetchone()
                if not supplier:
                    print(f"供应商 ID {supplier_id} 不存在，跳过")
                    continue # 跳过不存在的ID
                    
                supplier_name = supplier['name']
                
                # 检查关联产品
                cursor.execute("SELECT COUNT(*) as count FROM products WHERE supplier_id = %s", (supplier_id,))
                product_count = cursor.fetchone()['count']
                
                if product_count > 0:
                    suppliers_with_relations.append({
                        'id': supplier_id,
                        'name': supplier_name,
                        'product_count': product_count
                    })
                else:
                    valid_ids_to_delete.append(supplier_id)
                    
            except Exception as e:
                print(f"检查供应商 {supplier_id} 关联产品时出错: {str(e)}")
                # 如果检查出错，也视为无法删除，以保证数据安全
                suppliers_with_relations.append({
                    'id': supplier_id,
                    'name': f"ID:{supplier_id} (检查出错)",
                    'product_count': -1 # 表示检查出错
                })
        
        # 如果有任何供应商存在关联产品，则不允许删除任何供应商
        if suppliers_with_relations:
            conn.close()
            error_detail = ', '.join([f"{s['name']}(关联产品数: {s['product_count']})" for s in suppliers_with_relations])
            return jsonify({
                'status': 'error',
                'message': f'部分供应商无法删除，存在关联产品: {error_detail}',
                'suppliers_with_relations': suppliers_with_relations
            }), 400
            
        # 如果没有有效的ID可以删除 (例如所有ID都不存在或检查出错)
        if not valid_ids_to_delete:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '没有有效的供应商可供删除'
            }), 400

        # 执行批量删除
        deleted_count = 0
        try:
            # 使用 %s 占位符构建安全的 IN 查询
            placeholders = ', '.join(['%s'] * len(valid_ids_to_delete))
            sql = f"DELETE FROM suppliers WHERE id IN ({placeholders})"
            
            cursor.execute(sql, tuple(valid_ids_to_delete))
            deleted_count = cursor.rowcount
            conn.commit()
            print(f"成功删除 {deleted_count} 个供应商")
        except Exception as delete_error:
            conn.rollback()
            print(f"批量删除数据库操作失败: {delete_error}")
            conn.close()
            return jsonify({
                'status': 'error',
                'message': f'数据库删除操作失败: {delete_error}'
            }), 500
        finally:
            conn.close()
         
        return jsonify({
            'status': 'success',
            'message': f'成功删除 {deleted_count} 个供应商'
        })
        
    except Exception as e:
        print(f"批量删除供应商失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'批量删除供应商失败: {str(e)}'
        }), 500

    # Note: Removed duplicated get_warnings definition here
    try:
        print(f"删除供应商 ID: {supplier_id}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查供应商是否存在
        cursor.execute("SELECT id FROM suppliers WHERE id = %s", (supplier_id,))
        existing_supplier = cursor.fetchone()
        
        if not existing_supplier:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '供应商不存在'
            }), 404
        
        # 检查供应商是否有关联数据
        try:
            cursor.execute("SELECT COUNT(*) as count FROM products WHERE supplier_id = %s", (supplier_id,))
            product_count = cursor.fetchone()['count']
            
            if product_count > 0:
                conn.close()
                return jsonify({
                    'status': 'error',
                    'message': f'无法删除该供应商，存在{product_count}个关联产品',
                    'related_products': product_count
                }), 400
        except Exception as e:
            # 如果产品表不存在，则忽略此检查
            print(f"检查关联产品时出错: {str(e)}")
        
        # 执行删除
        cursor.execute("DELETE FROM suppliers WHERE id = %s", (supplier_id,))
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        if affected_rows > 0:
            return jsonify({
                'status': 'success',
                'message': '供应商删除成功'
            })
        else:
            return jsonify({
                'status': 'error',
                'message': '供应商删除失败'
            }), 500
    
    except Exception as e:
        print(f"删除供应商失败: {str(e)}")
    return jsonify({
        'status': 'error',
            'message': f'删除供应商失败: {str(e)}'
        }), 500

    # Note: Removed duplicated get_warnings definition here
    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        warning_type = request.args.get('type')
        keyword = request.args.get('keyword')
        offset = (page - 1) * page_size
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        where_clauses = []
        params = []
        
        if warning_type:
            where_clauses.append("ah.alert_type = %s")
            params.append(warning_type)
            
        if keyword:
                where_clauses.append("(p.name LIKE %s OR s.name LIKE %s OR pr.batch_number LIKE %s)")
                params.extend([f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"])
                
        where_clause = " AND ".join(where_clauses)
        if where_clause:
            where_clause = "WHERE " + where_clause
            
        # 获取总记录数
        count_sql = f"""
        SELECT COUNT(*) as total 
        FROM alert_history ah
        LEFT JOIN alert_rules ar ON ah.alert_rule_id = ar.id
        LEFT JOIN production_records pr ON ah.production_record_id = pr.id
        LEFT JOIN products p ON pr.product_id = p.id
        LEFT JOIN suppliers s ON p.supplier_id = s.id
        {where_clause}
        """
        cursor.execute(count_sql, params)
        total = cursor.fetchone()['total']
        
        # 获取分页数据
        sql = f"""
        SELECT ah.id, ah.alert_type, ah.alert_level, ah.alert_content, 
               ah.created_at as alert_time, ah.status,
               ah.resolved_at, ah.resolved_by,
               p.name as product_name, s.name as supplier_name,
               pr.batch_number as batch_number,
               ar.threshold_value, ar.condition_type,
               a.name as resolved_by_name
        FROM alert_history ah
        LEFT JOIN alert_rules ar ON ah.alert_rule_id = ar.id
        LEFT JOIN production_records pr ON ah.production_record_id = pr.id
        LEFT JOIN products p ON pr.product_id = p.id
        LEFT JOIN suppliers s ON p.supplier_id = s.id
        LEFT JOIN admins a ON ah.resolved_by = a.id
        {where_clause}
        ORDER BY ah.created_at DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(sql, params + [page_size, offset])
        warnings = cursor.fetchall()
        
        # 统计信息
        cursor.execute("SELECT COUNT(*) as total FROM alert_history")
        stats_total = cursor.fetchone()['total']
        
        cursor.execute("SELECT COUNT(*) as unprocessed FROM alert_history WHERE status = 'pending'")
        stats_unprocessed = cursor.fetchone()['unprocessed']
        
        cursor.execute("SELECT COUNT(*) as processed FROM alert_history WHERE status != 'pending'")
        stats_processed = cursor.fetchone()['processed']
        
        # 处理时间戳
        for warning in warnings:
            if 'alert_time' in warning and warning['alert_time']:
                warning['alert_time'] = warning['alert_time'].isoformat()
            if 'resolved_at' in warning and warning['resolved_at']:
                warning['resolved_at'] = warning['resolved_at'].isoformat()
        
        cursor.close()
        conn.close()
    
        return jsonify({
                'items': warnings, 
            'total': total,
            'stats': {
                    'total': stats_total,
                    'unprocessed': stats_unprocessed,
                    'processed': stats_processed
                }
            })
        
    except Exception as e:
        current_app.logger.error(f"获取预警列表失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/warnings/<int:warning_id>', methods=['GET'])
def get_warning_detail(warning_id):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        sql = """
        SELECT ah.*, ar.name as rule_name, ar.threshold_value, ar.condition_type,
               p.name as product_name, s.name as supplier_name,
               pr.batch_number, a.name as resolved_by_name
        FROM alert_history ah
        LEFT JOIN alert_rules ar ON ah.alert_rule_id = ar.id
        LEFT JOIN production_records pr ON ah.production_record_id = pr.id
        LEFT JOIN products p ON pr.product_id = p.id
        LEFT JOIN suppliers s ON p.supplier_id = s.id
        LEFT JOIN admins a ON ah.resolved_by = a.id
        WHERE ah.id = %s
        """
        cursor.execute(sql, (warning_id,))
        warning = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if not warning:
            return jsonify({'error': '预警记录不存在'}), 404
        
        # 处理时间戳
        if 'created_at' in warning and warning['created_at']:
            warning['created_at'] = warning['created_at'].isoformat()
        if 'updated_at' in warning and warning['updated_at']:
            warning['updated_at'] = warning['updated_at'].isoformat()
        if 'resolved_at' in warning and warning['resolved_at']:
            warning['resolved_at'] = warning['resolved_at'].isoformat()
            
        return jsonify(warning)
        
    except Exception as e:
        current_app.logger.error(f"获取预警详情失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/warnings/<int:warning_id>/process', methods=['POST'])
def process_warning(warning_id):
    try:
        data = request.get_json()
        process_method = data.get('method')
        process_comment = data.get('remark')
        
        if not process_method or not process_comment:
            return jsonify({'error': '处理方式和备注不能为空'}), 400
            
        # 获取当前管理员ID
        # 实际使用中需要从会话或JWT令牌中获取
        admin_id = 1  # 默认管理员ID
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 检查预警是否存在且未处理
        cursor.execute("SELECT status FROM alert_history WHERE id = %s", (warning_id,))
        warning = cursor.fetchone()
        
        if not warning:
            cursor.close()
            conn.close()
            return jsonify({'error': '预警记录不存在'}), 404
            
        if warning['status'] != 'pending':
            cursor.close()
            conn.close()
            return jsonify({'error': '该预警已处理'}), 400
            
        # 更新预警状态
        sql = """
        UPDATE alert_history 
        SET status = 'resolved', 
            resolved_by = %s, 
            resolved_at = %s,
            updated_at = %s
        WHERE id = %s
        """
        now = datetime.datetime.now()
        cursor.execute(sql, (admin_id, now, now, warning_id))
        
        conn.commit()
        cursor.close()
        conn.close()
            
        return jsonify({
            'message': '预警处理成功',
            'warning': {
            'id': warning_id,
            'status': 'resolved',
            'resolved_by': admin_id,
            'resolved_at': now.isoformat(),
            'method': process_method,
            'remark': process_comment
        }
    })
        
    except Exception as e:
        current_app.logger.error(f"处理预警失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/warnings/export', methods=['GET'])
def export_warnings():
    try:
        import pandas as pd
        import io
        
        warning_type = request.args.get('type')
        keyword = request.args.get('keyword')
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        where_clauses = []
        params = []
        
        if warning_type:
            where_clauses.append("ah.alert_type = %s")
            params.append(warning_type)
            
        if keyword:
            where_clauses.append("(p.name LIKE %s OR s.name LIKE %s OR pr.batch_number LIKE %s)")
            params.extend([f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"])
            
        where_clause = " AND ".join(where_clauses)
        if where_clause:
            where_clause = "WHERE " + where_clause
            
        # 获取所有符合条件的预警
        sql = f"""
        SELECT ah.id, ah.alert_type, ah.alert_level, ah.alert_content, 
               ah.created_at as alert_time, ah.status,
               ah.resolved_at,
               p.name as product_name, s.name as supplier_name,
               pr.batch_number as batch_number,
               ar.threshold_value, ar.condition_type,
               a.name as resolved_by_name
        FROM alert_history ah
        LEFT JOIN alert_rules ar ON ah.alert_rule_id = ar.id
        LEFT JOIN production_records pr ON ah.production_record_id = pr.id
        LEFT JOIN products p ON pr.product_id = p.id
        LEFT JOIN suppliers s ON p.supplier_id = s.id
        LEFT JOIN admins a ON ah.resolved_by = a.id
        {where_clause}
        ORDER BY ah.created_at DESC
        """
        cursor.execute(sql, params)
        warnings = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        # 将字典列表转换为DataFrame
        df = pd.DataFrame(warnings)
        
        # 重命名列
        columns_map = {
            'id': '预警ID',
            'alert_type': '预警类型',
            'alert_level': '预警级别',
            'alert_content': '预警内容',
            'alert_time': '预警时间',
            'status': '状态',
            'product_name': '产品名称',
            'supplier_name': '供应商',
            'batch_number': '批次号',
            'threshold_value': '阈值',
            'condition_type': '条件类型',
            'resolved_at': '处理时间',
            'resolved_by_name': '处理人'
        }
        df = df.rename(columns=columns_map)
        
        # 选择需要导出的列
        export_columns = [
            '预警ID', '预警类型', '预警级别', '预警内容', '预警时间',
            '产品名称', '供应商', '批次号', '阈值',
            '状态', '处理时间', '处理人'
        ]
        
        # 筛选出需要的列
        df = df[[col for col in export_columns if col in df.columns]]
        
        # 生成Excel文件
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='预警记录')
            # 自动调整列宽
            worksheet = writer.sheets['预警记录']
            for i, col in enumerate(df.columns):
                max_len = max(df[col].astype(str).map(len).max(), len(col)) + 2
                worksheet.set_column(i, i, max_len)
                
        output.seek(0)
        
        return send_file(
            output,
            as_attachment=True,
            download_name=f"预警记录_{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}.xlsx",
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        
    except Exception as e:
        current_app.logger.error(f"导出预警记录失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

# 获取批次列表接口
@app.route('/api/batches', methods=['GET'])
def get_batches():
    print(f"收到获取批次列表请求：{request.method} {request.path}")
    page = request.args.get('page', 1, type=int)
    pageSize = request.args.get('pageSize', 10, type=int)
    offset = (page - 1) * pageSize

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 获取总数
        # Assuming the table name is 'production_records' based on the existing POST route
        cursor.execute("SELECT COUNT(*) as total FROM production_records")
        total = cursor.fetchone()['total']

        # 获取分页数据
        # Adjusting the query based on the 'production_records' table structure
        query = """
            SELECT 
                pr.id, pr.batch_number, p.name as product_name, s.name as supplier_name, 
                pr.production_quantity, pr.unit, pr.production_date, pr.created_at
            FROM production_records pr
            JOIN products p ON pr.product_id = p.id
            JOIN suppliers s ON p.supplier_id = s.id
            ORDER BY pr.created_at DESC
            LIMIT %s OFFSET %s
        """
        cursor.execute(query, (pageSize, offset))
        batches = cursor.fetchall()
        
        # 格式化日期
        for batch in batches:
            if isinstance(batch['production_date'], datetime.date):
                batch['production_date'] = batch['production_date'].isoformat()
            if isinstance(batch['created_at'], datetime.datetime):
                 batch['created_at'] = batch['created_at'].isoformat()

        cursor.close()
        conn.close()

        return jsonify({'items': batches, 'total': total}), 200

    except Exception as e:
        print(f"获取批次列表失败: {e}")
        traceback.print_exc()
        return jsonify({'message': '获取批次列表失败'}), 500

# 创建新批次接口
@app.route('/api/batches', methods=['POST'])
# @token_required
def create_batch(): # Removed current_user argument as token_required is commented out
    print(f"收到创建批次请求：{request.method} {request.path}")
    try:
        data = request.get_json()
        product_id = data.get('product_id')
        storage_temp = data.get('storage_temp')
        production_quantity = data.get('production_quantity', 0)
        unit = data.get('unit', '件')
        quality_inspector = data.get('quality_inspector', '')
        
        if not product_id:
            return jsonify({'error': '产品ID不能为空'}), 400
            
        # 生成批次号: 产品代码-年月日-3位随机数
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 获取产品代码
        cursor.execute("SELECT code FROM products WHERE id = %s", (product_id,))
        product = cursor.fetchone()
        if not product:
            cursor.close()
            conn.close()
            return jsonify({'error': '产品不存在'}), 404
            
        product_code = product['code']
        current_date = datetime.datetime.now().strftime('%Y%m%d')
        random_num = ''.join(random.choices('0123456789', k=3))
        batch_number = f"{product_code}-{current_date}-{random_num}"
        
        # 插入生产记录
        sql = """
        INSERT INTO production_records 
        (product_id, batch_number, production_date, production_quantity, unit, quality_inspector, status, created_at) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        values = (product_id, batch_number, datetime.datetime.now().date(), production_quantity, unit, quality_inspector, 'completed', datetime.datetime.now())
        
        cursor.execute(sql, values)
        batch_id = cursor.lastrowid
        
        # 准备区块链存证数据
        cursor.execute("""
            SELECT p.id, p.name, p.code, p.category, p.shelf_life, p.storage_conditions,
                   s.name as supplier_name, s.code as supplier_code
            FROM products p
            JOIN suppliers s ON p.supplier_id = s.id
            WHERE p.id = %s
        """, (product_id,))
        product_info = cursor.fetchone()
        
        # 创建区块链存证记录
        blockchain_data = {
            'batch_number': batch_number,
            'product_id': product_id,
            'product_name': product_info['name'],
            'product_code': product_info['code'],
            'supplier_name': product_info['supplier_name'],
            'supplier_code': product_info['supplier_code'],
            'production_date': datetime.datetime.now().date().isoformat(),
            'shelf_life': product_info['shelf_life'],
            'storage_temp': storage_temp,
            'storage_conditions': product_info['storage_conditions']
        }
        
        # 生成数据哈希
        data_str = json.dumps(blockchain_data, sort_keys=True)
        data_hash = hashlib.sha256(data_str.encode()).hexdigest()
        
        # 获取最后一条记录的哈希作为前序哈希
        cursor.execute("SELECT block_hash FROM blockchain_records ORDER BY record_id DESC LIMIT 1")
        last_record = cursor.fetchone()
        prev_hash = last_record['block_hash'] if last_record else 'genesis'
        
        # 生成区块哈希
        block_hash = hashlib.sha256(f"{data_str}{prev_hash}".encode()).hexdigest()
        
        # 插入存证记录
        bc_sql = """
        INSERT INTO blockchain_records 
        (data_type, data_content, data_hash, prev_hash, block_hash, created_at) 
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        bc_values = ('batch', json.dumps(blockchain_data), data_hash, prev_hash, block_hash, datetime.datetime.now())
        
        cursor.execute(bc_sql, bc_values)
        blockchain_record_id = cursor.lastrowid
        
        # 更新批次记录中的区块链记录ID
        cursor.execute("UPDATE production_records SET blockchain_record_id = %s WHERE id = %s", 
                      (blockchain_record_id, batch_id))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({
        'message': '批次创建成功',
        'batch_id': batch_id,
        'batch_number': batch_number,
        'blockchain_record_id': blockchain_record_id
        })
        
    except Exception as e:
        current_app.logger.error(f"创建批次失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

# 获取批次列表
@app.route('/api/batches', methods=['GET'])
def get_batches():
    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        keyword = request.args.get('keyword', '')
        product_id = request.args.get('productId', '')
        
        offset = (page - 1) * page_size
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建基础查询
        base_query = """
        FROM production_records pr
        JOIN products p ON pr.product_id = p.id
        JOIN suppliers s ON p.supplier_id = s.id
        WHERE 1=1
        """
        
        # 添加查询条件
        conditions = []
        params = []
        
        if keyword:
            conditions.append("(pr.batch_number LIKE %s OR p.name LIKE %s OR s.name LIKE %s)")
            like_keyword = f"%{keyword}%"
            params.extend([like_keyword, like_keyword, like_keyword])
            
        if product_id:
            conditions.append("pr.product_id = %s")
            params.append(product_id)
            
        if conditions:
            base_query += " AND " + " AND ".join(conditions)
            
        # 获取总记录数
        count_sql = f"SELECT COUNT(*) as total {base_query}"
        cursor.execute(count_sql, params)
        total = cursor.fetchone()['total']
        
        # 获取分页数据
        data_sql = f"""
        SELECT pr.id, pr.batch_number, pr.production_date, pr.production_quantity, pr.unit,
               pr.quality_inspector, pr.status, pr.created_at,
               p.id as product_id, p.name as product_name, p.code as product_code,
               s.name as supplier_name
        {base_query}
        ORDER BY pr.created_at DESC
        LIMIT %s OFFSET %s
        """
        
        params.extend([page_size, offset])
        cursor.execute(data_sql, params)
        batches = cursor.fetchall()
        
        # 格式化日期
        for batch in batches:
            if 'created_at' in batch and batch['created_at']:
                batch['created_at'] = batch['created_at'].isoformat()
            if 'production_date' in batch and batch['production_date']:
                batch['production_date'] = batch['production_date'].isoformat()
                
        cursor.close()
        conn.close()
        
        return jsonify({'items': batches, 'total': total})
        
    except Exception as e:
        current_app.logger.error(f"获取批次列表失败: {str(e)}")
        traceback.print_exc() # 打印详细错误堆栈
        return jsonify({'error': f'获取批次列表时出错: {str(e)}'}), 500

@app.route('/api/batches/<int:batch_id>', methods=['GET'])
def get_batch_detail(batch_id):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 获取批次基本信息
        sql = """
        SELECT pr.id, pr.batch_number, pr.production_date, pr.production_quantity, pr.unit,
               pr.quality_inspector, pr.blockchain_record_id, pr.status, pr.created_at,
               p.id as product_id, p.name as product_name, p.code as product_code,
               p.category, p.shelf_life, p.storage_conditions,
               s.id as supplier_id, s.name as supplier_name, s.code as supplier_code
        FROM production_records pr
        JOIN products p ON pr.product_id = p.id
        JOIN suppliers s ON p.supplier_id = s.id
        WHERE pr.id = %s
        """
        cursor.execute(sql, (batch_id,))
        batch = cursor.fetchone()
        
        if not batch:
            cursor.close()
            conn.close()
            return jsonify({'error': '批次不存在'}), 404
            
        # 格式化日期字段
        if 'created_at' in batch and batch['created_at']:
            batch['created_at'] = batch['created_at'].isoformat()
        if 'production_date' in batch and batch['production_date']:
            batch['production_date'] = batch['production_date'].isoformat()
            
        # 获取区块链记录信息
        if batch['blockchain_record_id']:
            cursor.execute("SELECT * FROM blockchain_records WHERE record_id = %s", 
                         (batch['blockchain_record_id'],))
            blockchain_record = cursor.fetchone()
            if blockchain_record:
                if 'data_content' in blockchain_record and blockchain_record['data_content']:
                    blockchain_record['data_content'] = json.loads(blockchain_record['data_content'])
                if 'created_at' in blockchain_record and blockchain_record['created_at']:
                    blockchain_record['created_at'] = blockchain_record['created_at'].isoformat()
                    
                batch['blockchain_data'] = blockchain_record
                
        cursor.close()
        conn.close()
        
        return jsonify(batch)
        
    except Exception as e:
        current_app.logger.error(f"获取批次详情失败: {str(e)}")
        return jsonify({'error': str(e)}), 500

# 获取产品列表
@app.route('/api/products', methods=['GET'])
def get_products():
    try:
        print("=== 接收到获取产品列表请求 ===")
        # 获取查询参数
        # 获取查询参数
        fetch_all = request.args.get('fetch_all', 'false').lower() == 'true'
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        keyword = request.args.get('keyword', '')
        category = request.args.get('category', '')
        supplier_id = request.args.get('supplierId', '')
        
        print(f"查询参数: fetch_all={fetch_all}, page={page}, pageSize={page_size}, keyword='{keyword}', category='{category}', supplierId='{supplier_id}'")
        
        # 计算偏移量 (仅在非 fetch_all 模式下)
        offset = (page - 1) * page_size if not fetch_all else 0
        
        print("尝试获取数据库连接...")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        where_clause = "1=1"
        if keyword:
            where_clause += f" AND (p.name LIKE '%{keyword}%' OR p.code LIKE '%{keyword}%')"
        if category:
            where_clause += f" AND p.category = '{category}'"
        if supplier_id:
            where_clause += f" AND p.supplier_id = {supplier_id}"
        
        print(f"SQL查询条件: {where_clause}")
        
        # 查询总数
        try:
            count_sql = f"""
                SELECT COUNT(*) as total 
                FROM products p
                WHERE {where_clause}
            """
            print(f"执行SQL: {count_sql}")
            cursor.execute(count_sql)
            total = cursor.fetchone()['total']
            print(f"总记录数: {total}")
        except Exception as e:
            print(f"查询总数失败: {e}")
            traceback.print_exc()
            raise
        
        # 查询数据
        try:
            data_sql = f"""
                SELECT 
                    p.id,
                    p.name,
                    p.code,
                    p.category,
                    p.shelf_life,
                    p.storage_conditions,
                    p.description,
                    p.net_weight,
                    p.image_url,
                    p.status,
                    p.supplier_id,
                    s.name as supplier
                FROM products p
                LEFT JOIN suppliers s ON p.supplier_id = s.id
                WHERE {where_clause}
                ORDER BY p.id DESC 
                {'LIMIT ' + str(offset) + ', ' + str(page_size) if not fetch_all else ''}
            """
            print(f"执行SQL: {data_sql}")
            cursor.execute(data_sql)
            products = cursor.fetchall()
            print(f"查询到 {len(products)} 条记录")
            if products and len(products) > 0:
                print(f"第一条记录: {products[0]}")
        except Exception as e:
            print(f"查询数据失败: {e}")
            traceback.print_exc()
            raise
        
        # 关闭连接
        conn.close()
        print("数据库连接已关闭")
        
        # 创建响应对象
        print("创建响应...")
        response = jsonify({
            'status': 'success',
            'message': '获取成功',
            'total': total,
            'items': products
        })
        
        # 添加缓存控制头，确保前端获取最新数据
        response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
        response.headers['Pragma'] = 'no-cache'
        response.headers['Expires'] = '0'
        
        print("响应准备完成，返回数据")
        return response
    except Exception as e:
        error_msg = f"获取产品列表失败: {str(e)}"
        print(error_msg)
        print("详细错误信息:")
        traceback.print_exc()
        return jsonify({
            'status': 'error',
            'message': error_msg
        }), 500

# 添加产品
@app.route('/api/products', methods=['POST'])
def add_product():
    try:
        # 获取请求数据
        product = request.get_json()
        print(f"接收到添加产品请求: {product}")
        
        # 数据验证
        required_fields = ['name', 'category', 'supplier_id', 'shelf_life', 'storage_conditions']
        for field in required_fields:
            if field not in product or not product[field]:
                return jsonify({
                    'status': 'error',
                    'message': f'缺少必填字段: {field}'
                }), 400
        
        # 连接数据库
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 生成唯一产品编码
        today = datetime.datetime.now().strftime('%Y%m%d')
        cursor.execute("SELECT MAX(code) as max_code FROM products WHERE code LIKE %s", (f'SP-{today}-%',))
        result = cursor.fetchone()
        max_code = result['max_code']
        
        if max_code:
            # 提取序号并加1
            seq = int(max_code.split('-')[-1]) + 1
        else:
            # 当天第一个产品
            seq = 1
        
        # 格式化为三位数字
        code = f"SP-{today}-{seq:03d}"
        
        # 准备数据
        nutrition_info = None
        if 'nutrition_info' in product and product['nutrition_info']:
            nutrition_info = json.dumps(product['nutrition_info'])
        
        # 插入数据
        try:
            cursor.execute("""
                INSERT INTO products 
                (name, supplier_id, code, category, description, shelf_life, 
                storage_conditions, net_weight, nutrition_info, image_url, status) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                product['name'],
                product['supplier_id'],
                code,
                product['category'],
                product.get('description', ''),
                product['shelf_life'],
                product['storage_conditions'],
                product.get('net_weight', ''),
                nutrition_info,
                product.get('image_url', ''),
                product.get('status', 'active')
            ))
            
            # 获取新插入记录的ID
            new_id = cursor.lastrowid
            
            # 提交事务
            conn.commit()
            print(f"添加产品成功，ID: {new_id}")
            
            # 查询刚插入的记录完整信息（包括供应商名称）
            cursor.execute("""
                SELECT 
                    p.id,
                    p.name,
                    p.code,
                    p.category,
                    p.description,
                    p.shelf_life,
                    p.storage_conditions,
                    p.net_weight,
                    p.nutrition_info,
                    p.image_url,
                    p.status,
                    p.supplier_id,
                    s.name as supplier
                FROM products p
                LEFT JOIN suppliers s ON p.supplier_id = s.id
                WHERE p.id = %s
            """, (new_id,))
            
            new_product = cursor.fetchone()
            
            conn.close()
            
            return jsonify({
                'status': 'success',
                'message': '产品添加成功',
                'product': new_product
            })
            
        except Exception as e:
            conn.rollback()
            conn.close()
            error_msg = f"添加产品失败: {str(e)}"
            print(error_msg)
            return jsonify({
                'status': 'error',
                'message': error_msg
            }), 500
            
    except Exception as e:
        error_msg = f"添加产品请求处理失败: {str(e)}"
        print(error_msg)
        return jsonify({
            'status': 'error',
            'message': error_msg
        }), 500

# 获取单个产品
@app.route('/api/products/<product_id>', methods=['GET'])
def get_product(product_id):
    try:
        print(f"获取产品详情 ID: {product_id}")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 查询产品信息（包括供应商名称）
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.code,
                p.category,
                p.description,
                p.shelf_life,
                p.storage_conditions,
                p.net_weight,
                p.nutrition_info,
                p.image_url,
                p.status,
                p.supplier_id,
                s.name as supplier,
                p.created_at,
                p.updated_at
            FROM products p
            LEFT JOIN suppliers s ON p.supplier_id = s.id
            WHERE p.id = %s
        """, (product_id,))
        
        product = cursor.fetchone()
        conn.close()
        
        if product:
            # 处理日期字段，使其可序列化
            if 'created_at' in product and product['created_at']:
                product['created_at'] = product['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if 'updated_at' in product and product['updated_at']:
                product['updated_at'] = product['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
            
            # 处理JSON字段
            if product['nutrition_info'] and isinstance(product['nutrition_info'], str):
                try:
                    product['nutrition_info'] = json.loads(product['nutrition_info'])
                except:
                    product['nutrition_info'] = {}
                    
            return jsonify({
                'status': 'success',
                'product': product
            })
        else:
            return jsonify({
                'status': 'error',
                'message': '产品不存在'
            }), 404
    except Exception as e:
        print(f"获取产品详情失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'获取产品详情失败: {str(e)}'
        }), 500

# 更新产品
@app.route('/api/products/<product_id>', methods=['PUT'])
def update_product(product_id):
    try:
        product_data = request.get_json()
        print(f"更新产品 ID: {product_id}, 数据: {product_data}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查产品是否存在
        cursor.execute("SELECT id FROM products WHERE id = %s", (product_id,))
        existing_product = cursor.fetchone()
        
        if not existing_product:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '产品不存在'
            }), 404
        
        # 更新产品信息
        update_fields = []
        update_values = []
        
        # 动态构建更新字段
        field_mappings = {
            'name': 'name',
            'category': 'category',
            'supplier_id': 'supplier_id',
            'description': 'description',
            'shelf_life': 'shelf_life',
            'storage_conditions': 'storage_conditions',
            'net_weight': 'net_weight',
            'image_url': 'image_url',
            'status': 'status'
        }
        
        for client_field, db_field in field_mappings.items():
            if client_field in product_data and product_data[client_field] is not None:
                update_fields.append(f"{db_field} = %s")
                update_values.append(product_data[client_field])
        
        # 处理营养信息JSON字段
        if 'nutrition_info' in product_data and product_data['nutrition_info'] is not None:
            update_fields.append("nutrition_info = %s")
            update_values.append(json.dumps(product_data['nutrition_info']))
        
        # 添加更新时间
        update_fields.append("updated_at = NOW()")
        
        # 如果没有要更新的字段，直接返回成功
        if not update_fields:
            conn.close()
            return jsonify({
                'status': 'success',
                'message': '没有字段需要更新',
                'product': product_data
            })
        
        # 构建更新SQL
        update_sql = f"""
            UPDATE products 
            SET {', '.join(update_fields)}
            WHERE id = %s
        """
        
        # 添加ID作为WHERE条件的值
        update_values.append(product_id)
        
        # 执行更新
        cursor.execute(update_sql, update_values)
        conn.commit()
        
        # 获取更新后的产品信息
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.code,
                p.category,
                p.description,
                p.shelf_life,
                p.storage_conditions,
                p.net_weight,
                p.nutrition_info,
                p.image_url,
                p.status,
                p.supplier_id,
                s.name as supplier,
                p.created_at,
                p.updated_at
            FROM products p
            LEFT JOIN suppliers s ON p.supplier_id = s.id
            WHERE p.id = %s
        """, (product_id,))
        
        updated_product = cursor.fetchone()
        conn.close()
        
        # 处理日期字段，使其可序列化
        if updated_product:
            if 'created_at' in updated_product and updated_product['created_at']:
                updated_product['created_at'] = updated_product['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if 'updated_at' in updated_product and updated_product['updated_at']:
                updated_product['updated_at'] = updated_product['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
            
            # 处理JSON字段
            if updated_product['nutrition_info'] and isinstance(updated_product['nutrition_info'], str):
                try:
                    updated_product['nutrition_info'] = json.loads(updated_product['nutrition_info'])
                except:
                    updated_product['nutrition_info'] = {}
        
        return jsonify({
            'status': 'success',
            'message': '产品更新成功',
            'product': updated_product
        })
        
    except Exception as e:
        print(f"更新产品失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'更新产品失败: {str(e)}'
        }), 500

# 删除产品
@app.route('/api/products/<product_id>', methods=['DELETE'])
def delete_product(product_id):
    try:
        print(f"删除产品 ID: {product_id}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查产品是否存在
        cursor.execute("SELECT id FROM products WHERE id = %s", (product_id,))
        existing_product = cursor.fetchone()
        
        if not existing_product:
            conn.close()
            return jsonify({
                'status': 'error',
                'message': '产品不存在'
            }), 404
        
        # 检查产品是否有关联数据
        try:
            cursor.execute("SELECT COUNT(*) as count FROM production_records WHERE product_id = %s", (product_id,))
            record_count = cursor.fetchone()['count']
            
            if record_count > 0:
                conn.close()
                return jsonify({
                    'status': 'error',
                    'message': f'无法删除该产品，存在{record_count}个关联生产记录',
                    'related_records': record_count
                }), 400
        except Exception as e:
            # 如果生产记录表不存在，则忽略此检查
            print(f"检查关联生产记录时出错: {str(e)}")
        
        # 执行删除
        cursor.execute("DELETE FROM products WHERE id = %s", (product_id,))
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        if affected_rows > 0:
            return jsonify({
                'status': 'success',
                'message': '产品删除成功'
            })
        else:
            return jsonify({
                'status': 'error',
                'message': '产品删除失败'
            }), 500
            
    except Exception as e:
        print(f"删除产品失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'删除产品失败: {str(e)}'
        }), 500

# 批量删除产品
@app.route('/api/products/batch-delete', methods=['POST'])
def batch_delete_products():
    try:
        data = request.get_json()
        product_ids = data.get('ids', [])
        
        if not product_ids:
            return jsonify({
                'status': 'error',
                'message': '没有提供要删除的产品ID'
            }), 400
        
        print(f"批量删除产品 IDs: {product_ids}")
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 检查是否有关联数据
        products_with_relations = []
        for product_id in product_ids:
            try:
                cursor.execute("SELECT COUNT(*) as count FROM production_records WHERE product_id = %s", (product_id,))
                record_count = cursor.fetchone()['count']
                
                if record_count > 0:
                    cursor.execute("SELECT name FROM products WHERE id = %s", (product_id,))
                    product = cursor.fetchone()
                    product_name = product['name'] if product else f"ID:{product_id}"
                    products_with_relations.append({
                        'id': product_id,
                        'name': product_name,
                        'record_count': record_count
                    })
            except Exception as e:
                # 如果生产记录表不存在，则忽略此检查
                print(f"检查关联生产记录时出错: {str(e)}")
        
        if products_with_relations:
            conn.close()
            return jsonify({
                'status': 'error',
               'message': '部分产品无法删除，存在关联生产记录',
                'products_with_relations': products_with_relations
            }), 400
        
        # 执行批量删除
        deleted_count = 0
        for product_id in product_ids:
            cursor.execute("DELETE FROM products WHERE id = %s", (product_id,))
            deleted_count += cursor.rowcount
        
        conn.commit()
        conn.close()
         
        return jsonify({
            'status': 'success',
            'message': f'成功删除{deleted_count}个产品'
        })
        
    except Exception as e:
        print(f"批量删除产品失败: {str(e)}")
        return jsonify({
            'status': 'error',
            'message': f'批量删除产品失败: {str(e)}'
        }), 500

# 上传图片
@app.route('/api/upload', methods=['POST', 'OPTIONS'])
def upload_file():
    if request.method == 'OPTIONS':
        return '', 200
        
    if 'file' not in request.files:
        return jsonify({
            'status': 'error',
            'message': '未找到上传文件'
        }), 400
        
    file = request.files['file']
    if file.filename == '':
        return jsonify({
            'status': 'error',
            'message': '未选择文件'
        }), 400
        
    # 允许上传的图片格式
    allowed_extensions = {'png', 'jpg', 'jpeg', 'gif'}
    if not '.' in file.filename or file.filename.rsplit('.', 1)[1].lower() not in allowed_extensions:
        return jsonify({
            'status': 'error',
            'message': '不支持的文件类型，仅支持PNG、JPG、JPEG、GIF格式'
        }), 400
    
    try:
        # 创建上传目录（如果不存在）
        upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads')
        if not os.path.exists(upload_dir):
            os.makedirs(upload_dir)
        
        # 生成安全的文件名
        original_filename = secure_filename(file.filename)
        # 加上时间戳和随机数，确保唯一性
        timestamp = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
        random_suffix = f"{random.randint(1000, 9999)}"
        filename = f"{timestamp}_{random_suffix}_{original_filename}"
        
        # 保存文件
        file_path = os.path.join(upload_dir, filename)
        file.save(file_path)
        
        # 构建URL路径（相对路径）
        url = f"/uploads/{filename}"
        
        return jsonify({
            'status': 'success',
            'message': '上传成功',
            'url': url,
            'filename': filename
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': f'上传失败: {str(e)}'
        }), 500


# @app.route('/api/products', methods=['GET'])# 旧的产品列表
# def get_products_for_batch():
#     try:
#         conn = get_db_connection()
#         cursor = conn.cursor()
        
#         sql = """
#         SELECT p.id, p.name, p.code, p.category, s.name as supplier_name
#         FROM products p
#         JOIN suppliers s ON p.supplier_id = s.id
#         WHERE p.status = 'active'
#         ORDER BY p.name
#         """
#         cursor.execute(sql)
#         products = cursor.fetchall()
        
#         cursor.close()
#         conn.close()

#         return jsonify(products)
        
#     except Exception as e:
#         current_app.logger.error(f"获取产品列表失败: {str(e)}")
#         return jsonify({'error': str(e)}), 500

# 生成溯源码
@app.route('/api/batches/<int:batch_id>/qrcode', methods=['GET'])
def generate_qrcode(batch_id):
    try:
        import qrcode # type: ignore
        import io
        import base64
        
        # 获取批次基本信息
        conn = get_db_connection()
        cursor = conn.cursor()
        
        sql = """
        SELECT pr.batch_number, pr.blockchain_record_id,
               p.name as product_name, s.name as supplier_name
        FROM production_records pr
        JOIN products p ON pr.product_id = p.id
        JOIN suppliers s ON p.supplier_id = s.id
        WHERE pr.id = %s
        """
        cursor.execute(sql, (batch_id,))
        batch = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if not batch:
            return jsonify({'error': '批次不存在'}), 404
            
        # 生成溯源信息URL
        trace_url = f"{request.host_url}trace?batch={batch['batch_number']}"
        
        # 生成二维码
        qr = qrcode.QRCode(
            version=1,
            error_correction=qrcode.constants.ERROR_CORRECT_L,
            box_size=10,
            border=4,
        )
        qr.add_data(trace_url)
        qr.make(fit=True)
        
        img = qr.make_image(fill_color="black", back_color="white")
        
        # 转换为base64
        buffered = io.BytesIO()
        img.save(buffered)
        img_str = base64.b64encode(buffered.getvalue()).decode()
        
        return jsonify({
            'batch_number': batch['batch_number'],
            'product_name': batch['product_name'],
            'supplier_name': batch['supplier_name'],
            'qrcode_base64': img_str,
            'trace_url': trace_url
        })
        
    except Exception as e:
        current_app.logger.error(f"生成溯源码失败: {str(e)}")
        return jsonify({'error': str(e)}), 500
    
@app.route('/api/islogin', methods=['GET'])
@token_required
def is_login(current_user):
    return jsonify({
        'message': '已登录',
        'user': current_user
    })

def generate_short_code(company_name):
    """生成供应商简码：取前4字拼音首字母+3位随机数"""
    initials = ''.join([lazy_pinyin(c)[0][0].upper() for c in company_name[:4]])
    return f"{initials}{random.randint(100, 999)}"
    
@app.route('/api/suppliers/import', methods=['POST', 'OPTIONS'])
def import_suppliers():
    if request.method == 'OPTIONS':
        return '', 200
        
    if 'file' not in request.files:
        print("未找到上传文件")
        return jsonify({
            'status': 'error',
            'message': '未找到上传文件'
        }), 400
        
    file = request.files['file']
    if file.filename == '':
        print("未选择文件")
        return jsonify({
            'status': 'error',
            'message': '未选择文件'
        }), 400
        
    if not allowed_file(file.filename):
        print(f"不支持的文件类型: {file.filename}")
        return jsonify({
            'status': 'error',
            'message': '仅支持.xlsx和.xls格式的Excel文件'
        }), 400
    
    temp_file_path = None
    try:
        # 生成安全的文件名
        filename = secure_filename(file.filename)
        temp_file_path = os.path.join(UPLOAD_FOLDER, filename)
        print(f"保存临时文件: {temp_file_path}")
        
        # 保存临时文件
        file.save(temp_file_path)
        
        # 读取Excel文件
        print("开始读取Excel文件")
        df = pd.read_excel(temp_file_path)
        print(f"Excel列名: {df.columns.tolist()}")

        # 新增：转换列名为字符串类型（解决列名含不可见字符的问题）
        df.columns = df.columns.astype(str)
        
        # 检查所需列是否存在
        required_columns = ["公司名称", "简称", "主营业务", "联系方式", "总部地址", "成立时间"]
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            print(f"缺少必要的列: {missing_columns}")
            return jsonify({
                'status': 'error',
                'message': f'Excel文件缺少必要的列: {", ".join(missing_columns)}'
            }), 400
        
        # 处理数据并写入数据库
        print("开始处理数据并写入数据库")
        conn = get_db_connection()
        
        # 新增连接测试
        with conn.cursor() as test_cursor:
            test_cursor.execute("SELECT 1")
            print("数据库连接测试成功")
            
        cursor = conn.cursor()
        
        # 插入数据
        insert_count = 0
        error_count = 0
        error_messages = []
        
        # 移除对get_transaction_isolation的调用，改用更安全的事务处理
        print("开始导入数据...")
        
        for index, row in df.iterrows():
            try:
                # 处理日期格式
                found_time = None
                if pd.notna(row["成立时间"]):
                    try:
                        # 记录原始日期值以便调试
                        original_date = str(row["成立时间"]).strip()
                        print(f"处理第{index+2}行日期: '{original_date}'")
                        
                        # 将输入转换为字符串并清理
                        date_str = original_date
                        
                        # 处理年份格式（如：2019年、2011年）
                        if '年' in date_str:
                            year = date_str.replace('年', '')
                            print(f"解析到年份格式: {year}")
                            if year.isdigit() and len(year) == 4:
                                found_time = f"{year}-01-01"
                                print(f"转换为日期: {found_time}")
                            else:
                                error_messages.append(f"第{index + 2}行成立时间格式错误：{date_str}")
                                continue
                        # 处理纯数字年份（如：2019）
                        elif date_str.isdigit() and len(date_str) == 4:
                            found_time = f"{date_str}-01-01"
                            print(f"纯数字年份转换为日期: {found_time}")
                        else:
                            # 尝试解析完整日期
                            try:
                                parsed_date = pd.to_datetime(date_str)
                                found_time = parsed_date.strftime('%Y-%m-%d')
                                print(f"完整日期解析为: {found_time}")
                            except Exception as date_parse_error:
                                print(f"日期解析失败: {str(date_parse_error)}")
                                error_messages.append(f"第{index + 2}行成立时间格式错误：{date_str}")
                                continue
                    except Exception as e:
                        print(f"日期转换总体错误: {str(e)}")
                        error_messages.append(f"第{index + 2}行成立时间格式错误")
                        continue

                # 插入数据（添加调试日志）
                print(f"正在插入第{index+2}行数据, 成立时间={found_time}")
                cursor.execute("""
                    INSERT INTO suppliers 
                    (name, code, business_scope, phone, address, found_time, contact_person, status) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    str(row["公司名称"]).strip(),
                    str(row["简称"]).strip(),
                    str(row["主营业务"]).strip(),
                    str(row["联系方式"]).strip(),
                    str(row["总部地址"]).strip(),
                    found_time,
                    "系统导入",  # 联系人默认为"系统导入"
                    "active"    # 状态默认为"active"
                ))
                
                # 新增：实时提交测试（每10条提交一次）
                if insert_count % 10 == 0 and insert_count > 0:
                    conn.commit()
                    print(f"实时提交{insert_count}条数据")
                
                # 新增：获取最后插入ID验证
                last_id = cursor.lastrowid
                print(f"插入成功，最后ID：{last_id}")
                insert_count += 1
                
            except Exception as e:
                error_count += 1
                error_message = f"第{index + 2}行数据导入失败: {str(e)}"
                print(error_message)
                error_messages.append(error_message)
                continue
        
        # 新增：事务提交验证
        try:
            conn.commit()
            print(f"事务提交成功，影响行数：{insert_count}")
        except Exception as commit_error:
            print(f"事务提交失败：{str(commit_error)}")
            raise
        
        # 新增：验证实际插入数量
        cursor.execute("SELECT COUNT(*) as total FROM suppliers")
        new_count = cursor.fetchone()['total']
        print(f"插入后数据库总记录数：{new_count}")
        
        conn.close()
        
        # 清理临时文件
        if temp_file_path and os.path.exists(temp_file_path):
            os.remove(temp_file_path)
            print("清理临时文件成功")
        
        message = f"成功导入{insert_count}条数据"
        if error_count > 0:
            message += f"，{error_count}条数据导入失败"
            if error_messages:
                message += f"，错误信息：{'; '.join(error_messages)}"
        
        print(f"导入完成: {message}")
        return jsonify({
            'code': 200,
            'data': {
                'importedCount': insert_count,
                'failedCount': error_count,
                'errors': error_messages
            },
            'message': message
        })

    except Exception as e:
        print(f"导入过程出错: {str(e)}")
        # 清理临时文件
        if temp_file_path and os.path.exists(temp_file_path):
            os.remove(temp_file_path)
            
        return jsonify({
            'code': 500,
            'message': f'导入失败: {str(e)}',
            'data': None
        }), 500

# 导出Excel文件
@app.route('/api/suppliers/export', methods=['GET'])
def export_suppliers():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 查询所有供应商数据
        cursor.execute("""
            SELECT 
                name as '公司名称',
                code as '简称',
                business_scope as '主营业务',
                phone as '联系方式',
                address as '总部地址',
                DATE_FORMAT(found_time, '%Y-%m-%d') as '成立时间'
            FROM suppliers 
            ORDER BY id DESC
        """)
        suppliers = cursor.fetchall()
        
        if not suppliers:
            return jsonify({
                'status': 'error',
                'message': '没有可导出的数据'
            }), 404
        
        # 创建DataFrame
        df = pd.DataFrame(suppliers)
        
        # 创建临时文件
        temp_file_path = os.path.join(UPLOAD_FOLDER, f'suppliers_export_{datetime.datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx')
        
        # 导出到Excel
        df.to_excel(temp_file_path, index=False, engine='openpyxl')
        
        # 读取文件内容
        with open(temp_file_path, 'rb') as f:
            file_content = f.read()
        
        # 清理临时文件
        os.remove(temp_file_path)
        
        # 设置响应头
        response = app.response_class(
            response=file_content,
            status=200,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers['Content-Disposition'] = f'attachment; filename=suppliers_export_{datetime.datetime.now().strftime("%Y%m%d")}.xlsx'
        return response
        
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': f'导出失败: {str(e)}'
        }), 500

# 下载Excel模板
@app.route('/api/suppliers/template', methods=['GET'])
def download_template():
    try:
        # 创建示例数据
        template_data = {
            '公司名称': ['示例公司'],
            '简称': ['示例'],
            '主营业务': ['食品加工'],
            '联系方式': ['13800138000'],
            '总部地址': ['广东省广州市天河区'],
            '成立时间': ['2024-01-01']
        }
        
        # 创建DataFrame
        df = pd.DataFrame(template_data)
        
        # 创建临时文件
        temp_file_path = os.path.join(UPLOAD_FOLDER, 'supplier_template.xlsx')
        
        # 导出到Excel
        df.to_excel(temp_file_path, index=False, engine='openpyxl')
        
        # 读取文件内容
        with open(temp_file_path, 'rb') as f:
            file_content = f.read()
        
        # 清理临时文件
        os.remove(temp_file_path)
        
        # 设置响应头
        response = app.response_class(
            response=file_content,
            status=200,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers['Content-Disposition'] = 'attachment; filename=supplier_template.xlsx'
        return response
        
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': f'下载模板失败: {str(e)}'
        }), 500

# 添加一个简单的ping接口用于测试连接
@app.route('/api/ping', methods=['GET', 'OPTIONS'])
def ping():
    if request.method == 'OPTIONS':
        return '', 200
    return jsonify({"message": "pong", "status": "success"}), 200
        
# 全局OPTIONS处理
@app.route('/api/<path:path>', methods=['OPTIONS'])
def options_handler(path):
    return '', 200



# 在产品创建路由中添加存证逻辑
@app.route('/api/supplier/products', methods=['POST'])
def create_product():
    data = request.get_json()
    
    # --- 开始：模拟产品数据库插入逻辑 --- 
    # 在实际应用中，这里应该包含将产品信息保存到数据库的代码
    # 例如: conn = get_db_connection(); cursor = conn.cursor()
    # sql = "INSERT INTO products (name, category, supplier_id, price, ...) VALUES (%s, %s, ...)"
    # values = (data['name'], data['category'], data['supplier_id'], data['price'], ...)
    # cursor.execute(sql, values)
    # new_product_id = cursor.lastrowid
    # conn.commit()
    # 
    # # 插入后获取必要的字段，如 code 和 production_date
    # cursor.execute("SELECT code, production_date FROM products WHERE id = %s", (new_product_id,))
    # product_details = cursor.fetchone()
    # product_code = product_details.get('code', 'N/A') # 获取产品编码，如果不存在则为 'N/A'
    # production_date = product_details.get('production_date', datetime.datetime.now().date()) # 获取生产日期，如果不存在则用当天
    # cursor.close()
    # conn.close()
    
    # 使用占位符代替实际数据库获取的值
    new_product_id = 'TEMP_ID' # 模拟获取新产品ID
    product_code = data.get('code', f"CODE-{random.randint(100, 999)}") # 尝试从请求获取，否则生成临时编码
    production_date = data.get('production_date', datetime.datetime.now().date().isoformat()) # 尝试从请求获取，否则用当天日期
    # --- 结束：模拟产品数据库插入逻辑 ---

    # 准备区块链存证数据 (包含名称, 编码, 供应商ID, 生产日期)
    record_data = {
        'data_type': 'product_creation',
        'data_content': {
            'product_id': new_product_id, # 添加产品ID
            'name': data.get('name'),
            'code': product_code, 
            'supplier_id': data.get('supplier_id'),
            'production_date': production_date 
        }
    }
    create_blockchain_record(record_data)  # 调用通用存证方法
    
    # 返回成功响应 (模拟)
    return jsonify({'message': '产品创建成功并已记录上链 (模拟)', 'product_id': new_product_id}), 201

@app.route('/api/logistics', methods=['POST']) 
def create_logistics():
    data = request.get_json()
    
    # 物流信息存证
    record_data = {
        'data_type': 'logistics',
        'data_content': {
            'batch_no': data['batch_no'],
            'from': data['from'],
            'to': data['to'],
            'temp': data['temp']
        }
    }
    create_blockchain_record(record_data)
    
    # ... 原有物流记录逻辑...

# 新增存证工具方法
def create_blockchain_record(data):
    """通用存证方法"""
    from blockchain_api import create_record
    try:
        response = create_record(data)
        if response.status_code != 200:
            app.logger.error(f"存证失败: {response.json()}")
    except Exception as e:
        app.logger.error(f"存证服务异常: {str(e)}")

# 静态文件服务路由
@app.route('/uploads/<path:filename>')
def serve_uploaded_file(filename):
    upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads')
    return send_from_directory(upload_dir, filename)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)
